package com.xiaotu.dao.sw;

import com.xiaotu.vo.SwHotVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author zhangxiao
 * @create 2021-09-13 9:48
 */
public interface HotSwServiceMapper {

    @Select("<script>" +
            " SELECT " +
            " MAX( t1.order_num ) order_num, " +
            " t1.starid, " +
            " MAX( t2.NAME ) starname, " +
            " MAX( t2.imagesurl ) imagesurl, " +
            " GROUP_CONCAT( DISTINCT t5.tvname ) relatedWorks  " +
            "FROM " +
            " tab_sw_hot t1 " +
            " LEFT JOIN tab_star_info t2 ON t1.starid = t2.starid " +
            " LEFT JOIN tab_screenwriter_works t4 ON t1.starid = t4.starid " +
            " LEFT JOIN tab_tv_info t5 ON t4.tvid = t5.tvid  " +
            " AND t5.tvtype IN ( 1, 2, 3, 6 )  " +
            " AND t5.firstplaytime REGEXP ( '^[0-9]{4}-[0-9]{2}' )  " +
            " AND t5.firstplaytime > DATE_SUB( CURDATE( ), INTERVAL 3 MONTH )  " +
            "GROUP BY " +
            " t1.starid  " +
            "ORDER BY " +
            " order_num  " +
            "</script>")
    List<SwHotVo> getHotSwList();

}
